package DBLayer;

import java.sql.*;
import java.util.ArrayList;




import ModelLayer.Product;
import ModelLayer.Supplier;


public class DBProduct implements IFDBProduct{

	private Connection con;
	
	public DBProduct(){
		con = DbConnection1.getInstance().getDBcon();
	}
	
	
	
	
	@Override
	public ArrayList<Product> getAllProducts(boolean retrieveAssociation) {
		return miscWhere("", retrieveAssociation);
	}

	@Override
	public Product findProduct(int prodID, boolean retrieveAssociation) {	
		String wClause = " prod_id = '" + prodID + "'";
		return singleWhere(wClause, retrieveAssociation);
	}



	@Override
	public int updateProduct(Product product) {
			Product prodObj = product;
			int rc = -1;
			
			String query = "UPDATE Product SET "+
			"pname = '" + prodObj.getpName() + "', "+
			"pprice = '" + prodObj.getpPrice() + "', "+
			"sprice = '" + prodObj.getsPrice() + "', "+
			"rprice = '" + prodObj.getrPrice() + "', "+
			"origin = '" + prodObj.getOrigin() + "', "+
			"minstock = '" + prodObj.getMinStock() + "', "+
			"currentstock = '" + prodObj.getCurentStock() + "', "+
			"supcvr = '" + prodObj.getSupplier().getCvrNo() + "' "+
			" WHERE prod_id = '" + prodObj.getId() + "'";
            
			System.out.println("Update query:" + query);
		try{ // update product
 		Statement stmt = con.createStatement();
 		stmt.setQueryTimeout(5);
 	 	rc = stmt.executeUpdate(query);
                  
 	 	stmt.close();
	}//end try	
 	catch(Exception ex){
 	 	System.out.println("Update exception in product db: "+ex);
  	}
	return(rc);
	}
	
	
	
	private ArrayList<Product> miscWhere(String wClause, boolean retrieveAssociation)
	 {
	     ResultSet results;
	     ArrayList<Product> list = new ArrayList<Product>(); 
	     Product product= new Product();
	     String query =  buildQuery(wClause);
	  
	            try{ // read the employee from the database
	  Statement stmt = con.createStatement();
	   stmt.setQueryTimeout(5);
	   results = stmt.executeQuery(query);
	   
	 
	  while( results.next() ){
	        Product prodObj = new Product();
	        prodObj = buildProduct(results); 

	   
	   
	        	if(retrieveAssociation)
	        	{
	        		DBSupplier dbSupplier= new DBSupplier();
	        		Supplier supplier= dbSupplier.findSupplier(product.getSupplier().getCvrNo(),false);
	        		product.setSupplier(supplier);
               
               
	        		System.out.println("Supplier is selected");
	                
			   
	        	}   
			    list.add(prodObj);
	        	    

	        
	        stmt.close(); 
	  }//end loop
	  }//end try 
	   catch(Exception e){
	    System.out.println("Query exception - select: "+e);
	   e.printStackTrace();
	   }
	  return list;
	 }
	
	 //method to build the query
	 private String buildQuery(String wClause)
	 {
	     String query="SELECT pname, pprice, sprice , rprice, origin, minstock, currentstock, supcvr, prod_id  FROM Product ";
	  
	  if (wClause.length()>0)
	   query=query+" WHERE "+ wClause;
	   
	  return query;
	 }
	 
	 
	 private Product buildProduct(ResultSet results)
     {   Product prodObj = new Product();
         try{ // the columns from the table emplayee  are used
        	   prodObj.setpName(results.getString("pname"));
               prodObj.setpPrice(results.getDouble("pprice"));
               prodObj.setsPrice(results.getDouble("sprice"));
               prodObj.setrPrice(results.getDouble("rprice"));
               prodObj.setOrigin(results.getString("origin"));
               prodObj.setMinStock(results.getInt("minstock"));
               prodObj.setCurentStock(results.getInt("currentstock"));
               prodObj.setSupplier(new Supplier(results.getString("supcvr")));
               
               prodObj.setId(results.getInt("prod_id"));
               
               

               
     
         }
        catch(Exception e)
        {
            System.out.println("error in building the customer object");
        }
        return prodObj;
     }
	
	    public int insertProduct(Product prod) throws SQLException
	    {  //call to get the next ssn number
	        
	        int nextSSN = GetMax.getMaxId("Select max(prod_id) from Product");
	        nextSSN = nextSSN + 1;
	        System.out.println("next prod_id = " +  nextSSN);
	  
	       int rc = -1;
		   String query="INSERT INTO product(pname, pprice, sprice, rprice, origin, minstock, currentstock, supcvr, prod_id)  VALUES('"+
		  	 prod.getpName()  + "','"  +
			    	prod.getpPrice()  + "','"  +
	                        prod.getsPrice() + "','" +
	                        	prod.getrPrice()+ "','" + 
	                        			prod.getOrigin()+ "', '" +
	                        				prod.getMinStock() + "', '" + 
	                        					prod.getCurentStock() + "', '" +
	                        							prod.getSupplier().getCvrNo() + "', '" +
	                        									prod.getId() + "')";
	                     
	          
	      System.out.println("insert : " + query);		  					
	      try{ // insert new employee
	            Statement stmt = con.createStatement();
		 	  	stmt.setQueryTimeout(5);
		 	  	rc = stmt.executeUpdate(query);
		 	  	stmt.close();
			}//end try	
		 	catch(SQLException ex){
		 	  	System.out.println("Insert exception in product db: "+ex);
		   }
		   return(rc);
		}
	    
	private Product singleWhere(String wClause, boolean retrieveAssociation)
		{
			ResultSet results;
			Product product= new Product();
		
			 String query =  buildQuery(wClause);
	         System.out.println(query);
	try{ // read the employee from the database
	Statement stmt = con.createStatement();
	stmt.setQueryTimeout(5);
	results = stmt.executeQuery(query);

	if( results.next() ){
	                     product = buildProduct(results);
	                     //association is to be build
	                    
	                     stmt.close();
	                     if(retrieveAssociation)
	                     {   //The Supplier is to be build as well
	                                    	 
	                      DBSupplier dbSupplier= new DBSupplier();
	                      Supplier supplier= dbSupplier.findSupplier(product.getSupplier().getCvrNo(),false);
	                      product.setSupplier(supplier);
	                      
	                      
	                      System.out.println("Supplier is selected");
	                     
	                      
	                     }
	}
	                 else{ //no customer was found
	                     product = null;
	                 }
	}//end try 
	catch(Exception e){
	System.out.println("Query exception: "+e);
	}
	return product;
	}




	@Override
	public int delete(Product product) {
		int rc = -1;

		String query = "DELETE FROM Product WHERE Prod_ID ='" + product.getId()
				+ "'";
		System.out.println(query);
		try { // delete from OrderLine
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (Exception ex) {
			System.out.println("Delete exception in sales order db: " + ex);
		}
		return (rc);
	}
}
	


